package com.sykmgj;

import com.sap.conn.jco.JCoTable;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Properties;

public class JdbcConn {
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;

    public Connection getConn() throws Exception {

        Properties proes = new Properties();
        File file = new File(JarPathUtil.getJarPath()+File.separator+"SapSykm.properties");
        FileInputStream inputStream = null;
        try {
            inputStream = new FileInputStream(file);
            proes.load(inputStream);
        } catch (Exception e) {
            throw new Exception(e);
        }

        try {
//            Class.forName("oracle.jdbc.OracleDriver");
//            String url = "jdbc:oracle:thin:@192.168.0.110:1521:C1Plan";
//            this.conn = DriverManager.getConnection(url, "PLAN_ADMIN", "PLANADMIN2019");
            Class.forName("oracle.jdbc.OracleDriver");
            String url = proes.getProperty("jdbc.url");//"jdbc:oracle:thin:@192.168.0.110:1521:C1Plan";
            this.conn = DriverManager.getConnection(url, proes.getProperty("jdbc.username"), proes.getProperty("jdbc.password"));
        } catch (ClassNotFoundException e) {
            throw new RuntimeException("class not find !", e);
        } catch (SQLException e) {
            throw new RuntimeException("", e);
        }
        return this.conn;
    }

    public void insert(JCoTable table, String sql, String[] columns, String interfacename, String t) throws Exception {
        int n = 0;
        int i = 0;
        try {
            System.out.println(""+ t);
            this.conn = getConn();
            this.conn.setAutoCommit(false);
            this.pstmt = this.conn.prepareStatement(sql);
            for (i = 0; i < table.getNumRows(); i++) {
                table.setRow(i);
                for (int x = 0; x < columns.length; x++) {
                    this.pstmt.setString(x + 1, table.getString(columns[x]));
                }
                this.pstmt.addBatch();
                if (i % 50000 == 0 && i != 0) {
                    this.pstmt.executeBatch();
                    this.conn.commit();
                    n++;
                }
            }
            int[] rows = this.pstmt.executeBatch();
            this.conn.commit();
            n = n * 50001 + rows.length;
            log(interfacename, t, String.valueOf(n), "", "U");
            System.out.println(String.valueOf(n) + "");
        } catch (Exception e) {
            String message = e.getMessage().replaceAll("'", "\"");
            log(interfacename, t, String.valueOf(n), message, "E");
            failDeal("");
            e.printStackTrace();
        } finally {
            releaseResource();
        }
    }

    public void executesql(String sql) throws Exception {
        this.conn = getConn();
        try {
            this.pstmt = this.conn.prepareStatement(sql);
            this.pstmt.execute();
            this.conn.commit();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            releaseResource();
        }
    }

    public void log(String interfacename, String table, String state, String message, String status) throws Exception {
        this.conn = getConn();
        Date now = new Date();
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
        String sql = "INSERT INTO C1.FAGLLEXT_LOG VALUES('" + interfacename + "','" + table + "','" +
                dateFormat.format(now) + "','" + state + "','" + message + "','" + status + "')";
        try {
            this.pstmt = this.conn.prepareStatement(sql);
            this.pstmt.executeUpdate();
            this.conn.commit();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            releaseResource();
        }
    }

    public void failDeal(String reason) throws Exception {
        this.conn = getConn();
        String errSql = "update e7mappingpro.T_Trans_Job a set a.state = '2',a.error_info ='" + reason + "'  where a.job_id in (select max(t.job_id) from e7mappingpro.T_Trans_Job t )";
        try {
            this.pstmt = this.conn.prepareStatement(errSql);
            this.pstmt.executeUpdate();
            this.conn.commit();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            releaseResource();
        }
    }

    public void releaseResource() {
        if (this.rs != null) {
            try {
                this.rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (this.pstmt != null) {
            try {
                this.pstmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (this.conn != null) {
            try {
                this.conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}
